Retrieve the list of tables, indices and the associated SQL statements

Overview:

Every SQLite database has a special table named sqlite_master, which is a system created table.
The sqlite_master table of a database contains the list of tables, indices and other details specific to that database.
The type column of the sqlite_master table denotes what type of database object the record is about. The type column will hold the value "table" for a database table and the value "index" for a database index.
The sql column of the sqlite_master table contains the SQL statement with which a table or index has been created.

The sqlite3 is the python database client for SQLite database.
A database connection to an SQLite database can be created by calling the connect() method of the sqlite3 module.
Through the connection object a sqlite3.Cursor object can be obtained.
A SQL statement like a SELECT statement can be executed on the SQLite server by calling the execute() method of the cursor object and passing the SQL statement as parameter.
The Python example below queries the sqlite_master table and prints the list of tables and indices present in the SQLite main database loaded through the file info.dbs.

# Import the python module - sqlite3

import sqlite3

# Create database connection to a main database which is stored in a file

connectionObject = sqlite3.connect("info.dbs")

# Obtain a cursor object

cursorObject = connectionObject.cursor()

# Print the tables and indices present in the SQLite main database

cursorObject.execute("select * from SQLite_master")

tables = cursorObject.fetchall()

print("Listing tables and indices from main database:")

for table in tables:

print("Type of database object: %s"%(table[0]))

print("Name of the database object: %s"%(table[1]))

print("Table Name: %s"%(table[2]))

print("Root page: %s"%(table[3]))

print("SQL statement: %s"%(table[4]))

connectionObject.close()

Listing tables and indices from main database:

Type of database object: table

Name of the database object: tokens

Table Name: tokens

Root page: 2

SQL statement: CREATE TABLE tokens(id int, value varchar(12), weight int)

Type of database object: table

Name of the database object: routes

Table Name: routes

Root page: 3

SQL statement: CREATE TABLE routes(id int, start int, stop int,hops int )

Type of database object: index

Name of the database object: idx_index

Table Name: tokens

Root page: 4

SQL statement: CREATE INDEX idx_index on tokens(value)